package zhang.utils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class  JDBCUtils implements JDBCUtil{

	
	private Connection getConnection() {
		Connection conn = null;
		String url = "jdbc:mysql://localhost:3306/mysql_db";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		try {
			conn = DriverManager.getConnection(url,"root","123456");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		return conn;
	}

	@Override
	public void insert(Object obj) {
		Connection conn = getConnection();
		PreparedStatement ps = null;
		StringBuffer sb = new StringBuffer("insert into ");
		Class clazz = obj.getClass();
		sb.append(clazz.getSimpleName());
		Field fs[] = clazz.getDeclaredFields();
		sb.append("(");
		for(int i=0;i<fs.length;i++) {
        	
    		if(i==fs.length-1) {
    			sb.append(fs[i].getName());
    			sb.append(")");
    		}else {
    			sb.append(fs[i].getName());
    			sb.append(",");
    			
    		}
    		
    	}
    	sb.append(" values (");
    	for(int i=0;i<fs.length;i++) {
    		
    		if(i==fs.length-1) {
    			sb.append("?)");
    		}else
    		sb.append("?,");
    		
    	}
    	try {
			ps = conn.prepareStatement(sb.toString());
			for(int i=0;i<fs.length;i++) {
				fs[i].setAccessible(true);
				ps.setObject(i+1, fs[i].get(obj));
			}
			ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(null,ps,conn);
		}
		
	}

	@Override
	public <T> List<T> selectAll(Class<T> c) {
		 String sql = "select * from "+ c.getSimpleName();    
         Field[] field = c.getDeclaredFields();
         Connection conn = getConnection();
         List<T> list = new LinkedList<T>();
         PreparedStatement ps = null;
         ResultSet rs = null;
         T obj=null;
         try {
             ps = conn.prepareStatement(sql);  
             rs = ps.executeQuery(); 
            
            	 
             while(rs.next()) {
                  obj = c.newInstance();
                 for(int i = 0; i < field.length; i++) {      
                     field[i].setAccessible(true);           
                     field[i].set(obj, rs.getObject(field[i].getName()));
                 }
                 list.add(obj);
             }
         } catch (Exception e) {
             e.printStackTrace();
         }finally {    
             close(rs,ps,conn);
         }
        return list;
	}

	@Override
	public void delete(Object obj) {
		Connection conn = getConnection();
    	PreparedStatement ps = null;
        
        String sql = "";
        StringBuffer sb = new StringBuffer(sql);
        sb.append("delete ");
        sb.append("from ");
        sb.append(obj.getClass().getSimpleName());
        sb.append(" where id = ?");
        Class clazz = obj.getClass();
        Field f = null;
		try {
			f = clazz.getDeclaredField("id");
			f.setAccessible(true);
		} catch (Exception e1) {
			e1.printStackTrace();
		}
        try {
        	ps = conn.prepareStatement(sb.toString());
        	ps.setObject(1, f.get(obj));
        	ps.execute();
        
        } 
        
        catch (Exception e) {
            e.printStackTrace();
        }finally {    
            close(null,ps,conn);
        }
	}
	public void deleteSC(Object obj) {
		Connection conn = getConnection();
    	PreparedStatement ps = null;
        
        String sql = "";
        StringBuffer sb = new StringBuffer(sql);
        sb.append("delete ");
        sb.append("from ");
        sb.append(obj.getClass().getSimpleName());
        sb.append(" where id = ? ");
        sb.append("and cid = ?");
        System.out.println(sb.toString());
        Class clazz = obj.getClass();
        Field f = null;
		try {
			f = clazz.getDeclaredField("id");
			f.setAccessible(true);
        	ps = conn.prepareStatement(sb.toString());
        	ps.setObject(1, f.get(obj));
        	f=clazz.getDeclaredField("cid");
        	f.setAccessible(true);
        	ps.setObject(2,f.get(obj));
        	ps.execute();
        
        } 
        
        catch (Exception e) {
            e.printStackTrace();
        }finally {    
            close(null,ps,conn);
        }
	}

	@Override
	public void update(Object obj) {
		Class<?> c = obj.getClass();//��ȡobj��Class
    	StringBuffer sb = new StringBuffer("update "+ c.getSimpleName() +" set ");//����StringBuffer�����޸�SQL���Ĺ���
    	Field[] field = c.getDeclaredFields();//ͨ�������ȡ�������������
    	for(int i = 1; i < field.length; i++) {
    		if(i != field.length-1) {    //�ж��Ƿ�Ϊ���һ�����ԣ�������������Ӷ���
    			sb.append(field[i].getName()).append("=?,");
    		}else {    //��Ϊ���һ����������� where
    			sb.append(field[i].getName()).append("=? where ");
    		}
    	}
    	//Ĭ�ϵ�һ������Ϊ�������и���ʱͨ����һ�����Խ��и���
    	sb.append(field[0].getName() + "=?");
    	String sql = sb.toString()+";";
    	Connection conn = getConnection();//��ȡ���Ӷ���
    	PreparedStatement ps = null;
    	try {
    		ps = conn.prepareStatement(sql);
    		for(int i = 1; i < field.length; i++) {
    			field[i].setAccessible(true);//���ÿ��Է���˽������
    			ps.setObject(i, field[i].get(obj));//��Ԥ�����SQL����е� ? ���и�ֵ
    		}
    		field[0].setAccessible(true);
    		ps.setObject(field.length, field[0].get(obj));
    		ps.execute();//ִ��sql���
    	} catch (Exception e) {
    		e.printStackTrace();
    	}finally {
    		close(null,ps,conn);//�ر���������
    	}
	}

	@Override
	public void close(ResultSet rs, PreparedStatement ps,Connection conn) {
		try {
    		if(rs!=null) rs.close();
    		if(ps!=null) ps.close();
    		if(conn!=null) conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public <T> T selectOne(Class<T> c, int id) {
		String sql = "select * from "+ c.getSimpleName()+" where id="+"?";    
        Field[] field = c.getDeclaredFields();
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        T obj=null;
        try {
            ps = conn.prepareStatement(sql); 
            ps.setInt(1, id);
            rs = ps.executeQuery(); 
            obj = c.newInstance();
            while(rs.next()) {
                for(int i = 0; i < field.length; i++) {      
                    field[i].setAccessible(true);           
                    field[i].set(obj, rs.getObject(field[i].getName()));   
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {    
            close(rs,ps,conn);
            
        }
        return obj;
	}
	public <T> T selectOne(Class<T> c, String name) {
		String sql = "select * from "+ c.getSimpleName()+" where name="+"?";    
        Field[] field = c.getDeclaredFields();
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        T obj=null;
        try {
        	
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            rs = ps.executeQuery(); 
            obj = c.newInstance();
            while(rs.next()) {
                for(int i = 0; i < field.length; i++) {      
                    field[i].setAccessible(true);           
                    field[i].set(obj, rs.getObject(field[i].getName()));   
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }finally {    
            close(rs,ps,conn);
            
        }
        return obj;
	}
}
